package edu.ncsu.csc.itrust.dao.mysql;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import edu.ncsu.csc.itrust.DBUtil;
import edu.ncsu.csc.itrust.beans.OverrideReasonBean;
import edu.ncsu.csc.itrust.beans.PrescriptionBean;
import edu.ncsu.csc.itrust.beans.PrescriptionReportBean;
import edu.ncsu.csc.itrust.beans.loaders.OverrideReasonBeanLoader;
import edu.ncsu.csc.itrust.beans.loaders.PrescriptionBeanLoader;
import edu.ncsu.csc.itrust.beans.loaders.PrescriptionReportBeanLoader;
import edu.ncsu.csc.itrust.dao.DAOFactory;
import edu.ncsu.csc.itrust.exception.DBException;

/**
 * DAO for Prescriptions/Medications
 * @author Patrick Finegan & Bailey Hayes
 *
 */
public class OfficeVisitMedicationDAO {

	/** Obligatory parent object */
	private DAOFactory factory;
	
	/** Obligatory objects for Prescription(Report)Bean */
	private PrescriptionBeanLoader prescriptionLoader = new PrescriptionBeanLoader();
	private PrescriptionReportBeanLoader prescriptionReportBeanLoader = new PrescriptionReportBeanLoader();
	
	/**
	 * The typical constructor.
	 * @param factory The {@link DAOFactory} associated with this DAO, which is used for obtaining SQL connections, etc.
	 */
	public OfficeVisitMedicationDAO(DAOFactory factory) {
		this.factory = factory;
	}

	/**
	 * Returns all of the prescriptions associated with the given office visit
	 * 
	 * @param visitID The ID of the office visit.
	 * @return A java.util.List of prescriptions.
	 * @throws DBException
	 */
	public List<PrescriptionBean> getPrescriptions(long visitID) throws DBException {
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			conn = factory.getConnection();
			ps = conn.prepareStatement("Select * From OVMedication,NDCodes Where OVMedication.VisitID = ? "
					+ "AND NDCodes.Code=OVMedication.NDCode");
			ps.setLong(1, visitID);
			ResultSet rs = ps.executeQuery();
			return prescriptionLoader.loadList(rs);
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DBException(e);
		} finally {
			DBUtil.closeConnection(conn, ps);
		}
	}
	
	/**
	 * Adds a prescription bean to the database.
	 * @param pres The prescription bean to be added.
	 * @return The unique ID of the newly added bean.
	 * @throws DBException
	 */
	public long addPrescription(PrescriptionBean pres) throws DBException {
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			conn = factory.getConnection();
			ps = conn
					.prepareStatement("INSERT INTO OVMedication (VisitID,NDCode,StartDate,EndDate,Dosage,Instructions) VALUES (?,?,?,?,?,?)");
			prescriptionLoader.loadParameters(ps, pres);
			ps.executeUpdate();
			return DBUtil.getLastInsert(conn);
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DBException(e);
		} finally {
			DBUtil.closeConnection(conn, ps);
		}
	}
	
	/**
	 * Edits an existing prescription bean.
	 * 
	 * @param pres The newly updated prescription bean.
	 * @return A long indicating the ID of the newly updated prescription bean.
	 * @throws DBException
	 */
	public long editPrescription(PrescriptionBean pres) throws DBException {
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			conn = factory.getConnection();
			//ps = conn.prepareStatement("UPDATE OVMedication (VisitID,NDCode,StartDate,EndDate,Dosage,Instructions) VALUES (?,?,?,?,?,?)");
			String statement = "UPDATE OVMedication " +
				"SET VisitID=?, NDCode=?, StartDate=?, EndDate=?, Dosage=?, Instructions=? " +
				"WHERE ID=?";
			ps = conn.prepareStatement(statement);
			prescriptionLoader.loadParameters(ps, pres);
			ps.setLong(7, pres.getId());
			ps.executeUpdate();
			return DBUtil.getLastInsert(conn);
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DBException(e);
		} finally {
			DBUtil.closeConnection(conn, ps);
		}
	}
	
	/**
	 * Returns a list of information related to prescription reports given all of the office visits and the
	 * patient ID. The patient ID is necessary in case the office visit IDs are for different patients (the
	 * disambiguation is for security reasons).
	 * 
	 * @param ovIDs A java.util.List of Longs for the office visits.
	 * @param patientID A long representing the MID of the patient in question.
	 * @return A java.util.List of prescription reports.
	 * @throws DBException
	 */
	public List<PrescriptionReportBean> getPrescriptionReportsByDate(long patientID, String startDate, String endDate)
			throws DBException {
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			conn = factory.getConnection();
			ps = conn.prepareStatement("SELECT * FROM NDCodes, OVMedication, OfficeVisits "
					+ "WHERE NDCodes.Code=OVMedication.NDCode AND OVMedication.VisitID=OfficeVisits.ID "
					+ "AND PatientID=? AND ((DATE(?) < OVMedication.EndDate AND DATE(?) > OVMedication.StartDate)"
					+ "OR (DATE(?) > OVMedication.StartDate  AND DATE(?) < OVMedication.EndDate ) OR "
					+ "(DATE(?) <= OVMedication.StartDate AND DATE(?) >= OVMedication.StartDate)) "
					+ "ORDER BY VisitDate DESC");
			ps.setLong(1, patientID);
			ps.setString(2, startDate);
			ps.setString(3, startDate);
			ps.setString(4, endDate);
			ps.setString(5, endDate);
			ps.setString(6, startDate);
			ps.setString(7, endDate);
			ResultSet rs = ps.executeQuery();
			return prescriptionReportBeanLoader.loadList(rs);
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DBException(e);
		} finally {
			DBUtil.closeConnection(conn, ps);
		}
	}

	/**
	 * Returns a list of information related to prescription reports given all of the office visits and the
	 * patient ID. The patient ID is necessary in case the office visit IDs are for different patients (the
	 * disambiguation is for security reasons).
	 * 
	 * @param ovIDs A java.util.List of Longs for the office visits.
	 * @param patientID A long representing the MID of the patient in question.
	 * @return A java.util.List of prescription reports.
	 * @throws DBException
	 */
	public List<PrescriptionReportBean> getPrescriptionReports(List<Long> ovIDs, long patientID)
			throws DBException {
		OfficeVisitDAO temp = factory.getOfficeVisitDAO();
		
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			conn = factory.getConnection();
			String preps = temp.buildPreps(ovIDs.size());
			ps = conn.prepareStatement("SELECT * FROM NDCodes, OVMedication, OfficeVisits "
					+ "WHERE NDCodes.Code=OVMedication.NDCode AND OVMedication.VisitID=OfficeVisits.ID "
					+ "AND PatientID=? AND VisitID IN(" + preps + ") ORDER BY VisitDate DESC");
			ps.setLong(1, patientID); 
			temp.prepareOVIDs(ps, ovIDs);
			ResultSet rs = ps.executeQuery();
			return prescriptionReportBeanLoader.loadList(rs);
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DBException(e);
		} finally {
			DBUtil.closeConnection(conn, ps);
		}
	}

	/**
	 * Removes the given medication from its office visit
	 * 
	 * @param ovMedicationID The unique ID of the medication to be removed.
	 * @throws DBException
	 */
	public void removePrescription(long ovMedicationID) throws DBException {
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			conn = factory.getConnection();
			ps = conn.prepareStatement("DELETE FROM OVMedication WHERE ID=? ");
			ps.setLong(1, ovMedicationID);
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DBException(e);
		} finally {
			DBUtil.closeConnection(conn, ps);
		}
	}
	
	/**
	 * Adds an override reason bean to the database.
	 * @param orc The override reason bean to be added.
	 * @return The unique ID of the newly added bean.
	 * @throws DBException
	 */
	public long addReason(OverrideReasonBean orb) throws DBException {
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			conn = factory.getConnection();
			ps = conn
					.prepareStatement("INSERT INTO OVReactionOverride (OVMedicationID,OverrideCode,OverrideComment) VALUES (?,?,?)");
			ps.setLong(1, orb.getPresID());
			ps.setString(2, orb.getORCode());
			ps.setString(3, orb.getDescription());
			ps.executeUpdate();
			return DBUtil.getLastInsert(conn);
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DBException(e);
		} finally {
			DBUtil.closeConnection(conn, ps);
		}
	}
	
	/**
	 * Get all the Override Reasons for a Medication
	 * @param OVMedicationID -- Medication ID
	 * @return List of all Override reasons for a medication
	 * @throws DBException
	 */
	public List<OverrideReasonBean> getOverrideReasons(long OVMedicationID) throws DBException{
		OverrideReasonBeanLoader temp = new OverrideReasonBeanLoader();
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			conn = factory.getConnection();
			ps = conn.prepareStatement("SELECT * FROM OVReactionOverride "
					+ "WHERE OVMedicationID= ?");
			ps.setLong(1, OVMedicationID);			
			ResultSet rs = ps.executeQuery();
			return temp.loadListR(rs);
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DBException(e);
		} finally {
			DBUtil.closeConnection(conn, ps);
		}
	}
	
	/**
	 * Edits an existing OverrideReason bean.
	 * 
	 * @param orb The newly updated OverrideReason bean.
	 * @return A long indicating the ID of the newly updated OverrideReason bean.
	 * @throws DBException
	 */
	public long editOverrideReason(OverrideReasonBean orb) throws DBException {
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			conn = factory.getConnection();
			String statement = "UPDATE OVReactionOverride " +
				"SET OverrideCode=?, OverrideComment=? WHERE OVMedicationID=?";
			ps = conn.prepareStatement(statement);
			ps.setString(1, orb.getORCode());
			ps.setString(2, orb.getDescription());
			ps.setLong(3, orb.getPresID());
			ps.executeUpdate();
			return DBUtil.getLastInsert(conn);
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DBException(e);
		} finally {
			DBUtil.closeConnection(conn, ps);
		}
	}
	
}
